<?php

class m_pengajuan_barang extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    function get_count_all_barang() {
        $sql = "SELECT COUNT(*) 'total' FROM barang a
                JOIN group_barang b ON a.gpbarang_cd =b.gpbarang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd
                JOIN merk e ON a.merk_cd =e.merk_cd
                ";
        $query = $this->db->query($sql);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    //cek data
    function get_count_all_barang_by_kode($params) {
        $sql = "SELECT COUNT(*) 'total' FROM barang WHERE barang_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    function get_count_all_pengajuan_barang_by_pengadaan_id($params) {
        $sql = "SELECT COUNT(*) 'total' FROM pengajuan_barang WHERE pengadaan_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    //cek data
    function get_count_all_barang_by_kode_nama($params) {
        $sql = "SELECT COUNT(*) 'total' FROM barang WHERE barang_id = ? AND barang_nama = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    //cek data
    function get_count_barang_by_kategoribarang_cd($params) {
        $sql = "SELECT COUNT(*) 'total' FROM barang WHERE kategoribarang_cd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get user profil
    function get_all_barang_pagination($params) {
        $sql = "SELECT a.*, b.gpbarang_name, c.kategoribarang_name, d.jenisbarang_name, e.merk_name FROM barang a
                JOIN group_barang b ON a.gpbarang_cd =b.gpbarang_cd
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd
                JOIN merk e ON a.merk_cd =e.merk_cd             
                ORDER BY a.barang_id ASC
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get user profil
    function get_pengajuan_barang_by_pengadaan_id_pagination($params) { 
        $sql = "SELECT a.*,  c.kategoribarang_name, d.jenisbarang_name, e.merk_name, f.lokasi_name 
                FROM pengajuan_barang a
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                LEFT JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd
                LEFT JOIN merk e ON a.merk_cd =e.merk_cd
                JOIN lokasi f ON a.lokasi_cd =f.lokasi_cd
                WHERE a.pengadaan_id = ?
                GROUP BY a.pengajuanbarang_id
                ORDER BY a.pengajuanbarang_nama ASC
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    } 
    
    // get user profil
    function get_all_pengajuan_barang_by_pengadaan_id($params) {
        $sql = "SELECT a.*,  c.kategoribarang_name, d.jenisbarang_name, e.merk_name, f.lokasi_name 
                FROM pengajuan_barang a
                JOIN kategoribarang c ON a.kategoribarang_cd =c.kategoribarang_cd
                JOIN jenisbarang d ON a.jenisbarang_cd =d.jenisbarang_cd
                JOIN merk e ON a.merk_cd =e.merk_cd
                JOIN lokasi f ON a.lokasi_cd =f.lokasi_cd
                WHERE a.pengadaan_id = ?
                GROUP BY a.pengajuanbarang_id
                ORDER BY a.pengajuanbarang_nama DESC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_all_barang_by_pengadaan_id($params) {
        $sql = "SELECT a.*, b.gpbarang_cd 
                FROM pengajuan_barang a LEFT JOIN kategoribarang b ON a.kategoribarang_cd = b.kategoribarang_cd
                WHERE a.pengadaan_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_pengajuan_barang_by_pengajuanbarang_id($params) {
        $sql = "SELECT a.* FROM pengajuan_barang a
                WHERE a.pengajuanbarang_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    function get_barang_by_noinventaris($params) {
        $sql = "SELECT * FROM barang WHERE barang_noinventaris = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // insert
    function insert($user_id) {
        // update by this date
        $sql = "INSERT INTO pengajuan_barang(
                pengajuanbarang_nama, pengajuanbarang_qty, pengajuanbarang_hargasatuan,
                pengajuanbarang_keterangan, pengajuanbarang_garansi, pengajuanbarang_tgl_garansi,
                pengajuanbarang_susut, pengajuanbarang_penyusutan, pengajuanbarang_status,
                pengajuanbarang_barang_kondisi, pengajuanbarang_generate_noiventaris, gpbarang_cd, 
                vendor_cd, lokasi_cd, ruang_cd, kategoribarang_cd, jenisbarang_cd, merk_cd, 
                pengadaan_id, mdb, mdd) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        return $this->db->query($sql, $user_id);
    }

    // edit
    function edit($user_id) {
        // update by this date
        $sql = "UPDATE pengajuan_barang 
                SET  pengajuanbarang_nama = ?, pengajuanbarang_qty = ?, pengajuanbarang_hargasatuan = ?,
                pengajuanbarang_keterangan = ?, pengajuanbarang_garansi = ?, pengajuanbarang_tgl_garansi = ?,
                pengajuanbarang_susut = ?, pengajuanbarang_penyusutan = ?, pengajuanbarang_status = ?,
                pengajuanbarang_barang_kondisi = ?, pengajuanbarang_generate_noiventaris = ?, gpbarang_cd = ?, 
                vendor_cd = ?, lokasi_cd = ?, ruang_cd = ?,  kategoribarang_cd = ?, jenisbarang_cd = ?, 
                merk_cd = ?, pengadaan_id = ?, mdb = ?, mdd = NOW()
                WHERE pengajuanbarang_id = ?";
        return $this->db->query($sql, $user_id);
    }

    // delete
    function delete($user_id) {
        // update by this date
        $sql = "DELETE FROM pengajuan_barang WHERE pengajuanbarang_id = ?";
        return $this->db->query($sql, $user_id);
    }

    function delete_by_pengadaan_id($user_id) {
        // update by this date
        $sql = "DELETE FROM pengajuan_barang WHERE pengadaan_id = ?";
        return $this->db->query($sql, $user_id);
    }

    function get_total_by_pengadaan_id($params) {
        $sql = "SELECT SUM(pengajuanbarang_hargasatuan * pengajuanbarang_qty) AS 'total' FROM pengajuan_barang WHERE pengadaan_id = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    function get_all_pengajuan_barang_by_pengajuanbarang_id($params) {
        $sql = "SELECT b.*, c.ruang_nomor
                FROM pengadaan a JOIN barang b ON a.pengadaan_id = b.pengadaan_id 
                LEFT JOIN ruang c ON c.ruang_cd = b.ruang_cd
                WHERE a.pengadaan_id = ? AND b.pengajuanbarang_id = ?
                ORDER BY b.barang_noinventaris ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
}
